package com.oa;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.junit.jupiter.api.Test;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class PoiTest1 {
    @Test
    public void TestExportExcel() throws IOException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日HH时mm分ss秒");
        String[] list = {"1", "2", "3", "4", "5", "6", "7", "8"};
        List<Edu1> eduList = new ArrayList<>();
        eduList.add(new Edu1(1, "1", new Date(), "1"));
        eduList.add(new Edu1(2, "1", new Date(), "2"));
        eduList.add(new Edu1(3, "1", new Date(), "3"));
        eduList.add(new Edu1(4, "1", new Date(), "4"));
        eduList.add(new Edu1(5, "1", new Date(), "5"));
        eduList.add(new Edu1(6, "1", new Date(), "6"));
        eduList.add(new Edu1(7, "1", new Date(), "7"));
        eduList.add(new Edu1(8, "1", new Date(), "8"));

        HSSFWorkbook workbook = new HSSFWorkbook();

        //设置标题单元格样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        //单元格对齐方式:居中对齐
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置单元格的前景色和背景色
        titleStyle.setFillForegroundColor(HSSFColor.LIME.index);
        titleStyle.setFillBackgroundColor(HSSFColor.LIME.index);
        titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //设置边框
        titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置标题字体样式
        HSSFFont titleFont = workbook.createFont();
        //设置字体为宋体
        titleFont.setFontName("宋体");
        //设置字体加粗
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体大小
        titleFont.setFontHeightInPoints((short) 15);
        //设置字体颜色
        titleFont.setColor(HSSFColor.BLACK.index);
        //将字体对象赋值给单元格样式对象
        titleStyle.setFont(titleFont);

        //设置正文单元格样式
        HSSFCellStyle contextStyle = workbook.createCellStyle();
        //设置居中
        contextStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        contextStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置日期类型显示格式
        contextStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd-HH-mm-ss"));
        //设置边框
        contextStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        contextStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        contextStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        contextStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置正文字体样式
        HSSFFont contextFont = workbook.createFont();
        contextFont.setFontName("宋体");
        //字体大小
        contextFont.setFontHeightInPoints((short) 15);
        //字体颜色
        contextFont.setColor(HSSFColor.BLACK.index);
        contextStyle.setFont(contextFont);

        int sheetNum = 0;
        int sheetIndex = 0;
        int sheetCount = 2;
        int columnIndex = 0;
        HSSFSheet tableSheet = null;
        Edu1 edu = null;

        //创建引用sheet
        Sheet hideSheet = workbook.createSheet("hiddenSheet");
        for (int i = 0; i < list.length; i++) {
            hideSheet.createRow(i).createCell(0).setCellValue(list[i]);
            sheetIndex++;
        }

        System.out.println(sheetIndex);

        Name hideSheetName = workbook.createName();
        hideSheetName.setNameName("hidden");
        hideSheetName.setRefersToFormula("hiddenSheet!" + "$A$1:$A$" + list.length);

        tableSheet = workbook.createSheet("路口信息");
        HSSFRow row = tableSheet.createRow(0);
        setCellStyle(row.createCell(0), titleStyle, "ID");
        setCellStyle(row.createCell(1), titleStyle, "学历");
        setCellStyle(row.createCell(2), titleStyle, "获得时间");
        setCellStyle(row.createCell(3), titleStyle, "下拉选择");
        tableSheet.setColumnWidth(2, 120 * 90);
        tableSheet.setColumnWidth(3, 120 * 50);

        DataValidationHelper helper = tableSheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden");
        //设置下拉框位置
        CellRangeAddressList addressList = new CellRangeAddressList(1, sheetCount, 3, 3);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        //数据校验
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
        //作用在目标sheet上
        tableSheet.addValidationData(dataValidation);

//        workbook.setSheetHidden(0, true);

        for (int i = 0; i < (eduList.size() / sheetCount); i++) {
            workbook.cloneSheet(workbook.getSheetIndex("路口信息"));
        }

        for (int i = 0; i < eduList.size(); i++) {
            edu = eduList.get(i);
            int rowIndex = i % 2 + 1;
            HSSFSheet sheet = workbook.getSheetAt(rowIndex);
            HSSFRow row1 = sheet.createRow(rowIndex);
            setCellStyle(row1.createCell(0), contextStyle, edu.getId().toString());
            setCellStyle(row1.createCell(1), contextStyle, edu.getEduName());
            setCellStyle(row1.createCell(2), contextStyle, format.format(edu.getTime()));
            setCellStyle(row1.createCell(3), contextStyle, edu.getList());
        }
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(new File("D:/\\test.xls"));
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                outputStream.close();
            }
        }
    }

    /**
     * 设置单元格样式
     *
     * @param cell      细胞
     * @param cellStyle 单元格样式
     * @param value     价值
     * @return {@link HSSFCell}
     */
    public static HSSFCell setCellStyle(HSSFCell cell, HSSFCellStyle cellStyle, String value) {
        cell.setCellStyle(cellStyle);
        cell.setCellValue(value);
        return cell;
    }
}
